
clear
version 16.0
set varabbrev off, permanently
cd C:\Users\User\Downloads\coronavirusstatelaws
makenfile 202coronaactions_uoa_state

*AGE
*Get number of civilians by age from census estimates data.
import delimited 001sc-est2018-agesex-civ.csv
drop if sumlev==10|age==999
recode age (18/24=1) (25/34=2) (35/44=3) (45/64=4) (65/max=5)
gen age2="18to24" if age==1
replace age2="25to34" if age==2
replace age2="35to44" if age==3
replace age2="45to64" if age==4
replace age2="65plus" if age==5
drop if age2==""
rename popest2018_civ civ2018_
collapse (sum) civ2018_, by(name age2)
rename (name age2) (state age)
reshape wide civ2018_, i(state) j(age) string
save $nfile, replace

*HISP
*Get number of hispanics.
drop _all
import delimited 002sc-est2018-alldata5.csv
drop if origin!=2|sex!=0
rename popestimate2018 hisp2018
collapse (sum) hisp2018, by(name)
rename name state
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*RACE ALONE OR IN COMBO
*Get number asians, blacks, native americans, and pactific islanders alone or in combo.
drop _all
import delimited 002sc-est2018-alldata5.csv
gen race2="asian" if race==4
replace race2="black" if race==2
replace race2="natam" if race==3
replace race2="pac" if race==5
drop if origin!=0|sex!=0|race2==""
rename popestimate2018 v
collapse (sum) v, by(name race2)
rename (name race2) (state race)
reshape wide v, i(state) j(race) string
rename (vasian vblack vnatam vpac) (asian2018 black2018 natam2018 pac2018)
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*WHITENH
*Get number of people who are white with no other race and non-Hispanic.
drop _all
import delimited 003sc-est2018-alldata6.csv
keep if sex==0&origin==1&race==1
rename popestimate2018 whitenh2018
collapse (sum) whitenh2018, by(name)
rename name state
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*POP
*Get population that is consistent with the race data.
drop _all
import delimited 003sc-est2018-alldata6.csv
keep if sex==0&origin==0
rename popestimate2018 pop2018
collapse (sum) pop2018, by(name)
rename name state
merge 1:1 state using $nfile
assert _merge==3
drop _merge
foreach r in asian black natam pac hisp whitenh {
replace `r'2018=`r'2018/pop2018
}
drop pop2018
save $nfile, replace

*TURNOUT x AGE
drop _all
import excel 004table04c_turnout_x_age_reworked.xlsx, firstrow
replace state=lower(state)
reshape wide pop cit voted, i(state) j(age) string
order state pop* cit* voted*
foreach v in pop cit voted {
egen `v'=rsum(`v'*)
gen `v'65plusper=`v'65plus/`v'
drop `v'
}
replace state=proper(state)
replace state="District of Columbia" if state=="District Of Columbia"
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*CORONA LAWS
drop _all
import excel 005coronalaws20200326fromworker.xlsx, firstrow
*The freevaccine variable isn't useful / something we're going to look at.
drop orderonmap freevaccine
tostring largegatheringbans paidsickleave nonessentialbizclosures mandatoryquarantine barrestaurantlimits schoolclosures, replace
merge 1:1 state using $nfile
assert state=="District of Columbia" if _merge==2
count if _merge==2
assert `r(N)'==1
drop _merge
save $nfile, replace
foreach v in largegatheringbans paidsickleave nonessentialbizclosures mandatoryquarantine barrestaurantlimits schoolclosures {
drop _all
import excel 005coronalaws20200326fromworker.xlsx, firstrow sheet(key)
keep `v' `v'2
merge 1:m `v' using $nfile
drop if _merge==1
assert state=="District of Columbia" if _merge==2
count if _merge==2
assert `r(N)'==1
drop _merge
save $nfile, replace
}
rename (largegatheringbans paidsickleave nonessentialbizclosures mandatoryquarantine barrestaurantlimits schoolclosures) (gather sickleave banbiz quar barsrest school)
rename (largegatheringbans2 paidsickleave2 nonessentialbizclosures2 mandatoryquarantine2 barrestaurantlimits2 schoolclosures2) (gather2 sickleave2 banbiz2 quar2 barsrest2 school2)
save $nfile, replace

*MISC DATA
drop _all
import excel 006misc_data20200326.xlsx, firstrow
gen leg=(sen_dem_control+house_dem_control)/2
rename gov_dem gov
drop n sen_total sen_dem sen_repub sen_nmp sen_vac house_total house_dem house_repub house_nmp house_vac legparty governorparty stategovernmentparty sen_dem_control house_dem_control statemaporder cases cases2 deaths2 pop18plus_20190701
rename pop20190701 pop2019
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*METR0-STATE INTERSECTIONS
*MCDC FILE
drop _all
import delimited 007a3_geocorr2018_20200327reworked.csv, varnames(1)
drop if sab=="DC"
makestatev
drop sfips sab sid
*Get total state pop.
replace pop2016=subinstr(pop2016,",","",.)
destring pop2016, replace
bysort state (pop2016): gen temp=sum(pop2016)
bysort state (temp): replace temp=temp[_N]
drop if metro=="999"
gen popcon2016=(pop2016/temp)*(pop2016/temp)
collapse (sum) popcon2016, by(state)
merge 1:1 state using $nfile
count if _merge==3
assert `r(N)'==47
drop _merge
replace popcon2016=1 if state=="District of Columbia"
replace popcon2016=0 if popcon2016==.
save $nfile, replace

*HOSPITALBEDS
drop _all
import delimited 008hospitalbeds2018_20200327reworked.csv, varnames(1)
rename ïstate state
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace

*STATEGDP
drop _all
import excel 011state_gdp_quarterly_growth20200328reworked.xlsx, firstrow
replace state=stritrim(strtrim(state))
merge 1:1 state using $nfile
assert _merge==3
drop _merge
save $nfile, replace
